#!/usr/bin/env python
# -*- coding: utf-8 -*-

import os
import sys
import glob
import _mysql, _mysql_exceptions
import loadSummaryToMysql
def connect_to_base_dicts(db='dbwww58com_58_stats'):
    host = '10.5.20.16'
    port = 58885
    user = 'ec_stat'
    passwd = 'L#c8UM5RGh5C'
    dbh = _mysql.connect(host, user, passwd, db, port)
    return dbh

def query(dbh, q):
    try:
        dbh.query(q)
    except _mysql.MySQLError, e:
        print e
        print q
        return None

    result = dbh.store_result()
    if result is None:  # the sql no response
        return True
    rows = result.fetch_row(0)
    return rows

#get table_prefix
def get_channel_info():
    q = 'select channel_id, table_prefix from channel_define'
    info = {}
    conn = connect_to_base_dicts('dbwww58com_base_dicts')
    r = query(conn, q)
    for one in r:
        info[one[0]] = one[1]
    conn.close()
    return info

def get_cateID_channelID():
    info = {}
    conn = connect_to_base_dicts('dbwww58com_58_stats')
    sql = 'select cate_id,channel_id from belong_cate'
    r = query(conn, sql)
    for one in r:
        info[one[0]] = one[1]
    conn.close()
    return info


def create_table(dbh,tablename):
    q = '''\
CREATE TABLE if not exists %s (
  `stat_date` date NOT NULL COMMENT '统计日期',
   `local_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '区域id',
  `dispcity1` varchar(50) NOT NULL DEFAULT '' COMMENT '一级城市',
  `dispcity2` varchar(50) NOT NULL DEFAULT '' COMMENT '二级城市',
  `dispcity3` varchar(50) NOT NULL DEFAULT '' COMMENT '三级城市',
  `cate_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '类别id',
  `belong_cate1` varchar(50) NOT NULL DEFAULT '' COMMENT '一级类别',
  `belong_cate2` varchar(50) NOT NULL DEFAULT '' COMMENT '二级类别',
  `total_info_count` int(11) DEFAULT '0' COMMENT '总帖子数',
  `total_info_users` int(11) DEFAULT '0' COMMENT '总发帖人数',
  `valid_info_count` int(11) DEFAULT '0' COMMENT '总有效帖子数',
  `valid_info_users` int(11) DEFAULT '0' COMMENT '总有效发帖人数',
  `new_info_count` int(11) DEFAULT '0' COMMENT '总新发帖子数',
  `new_info_users` int(11) DEFAULT '0' COMMENT '总新发帖子人数',
  `modify_info_count` int(11) DEFAULT '0' COMMENT '总修改帖子数',
  `modify_info_users` int(11) DEFAULT '0' COMMENT '总修改帖子人数',
  `refresh_info_count` int(11) DEFAULT '0' COMMENT '总刷新帖子数',
  `refresh_info_users` int(11) DEFAULT '0' COMMENT '总刷新帖子人数',
  `new_valid_info_count` int(11) DEFAULT '0' COMMENT '新增有效帖子数',
  `new_valid_info_users` int(11) DEFAULT '0' COMMENT '新增有效发帖人数',
  `ordinary_valid_info_count` int(11) DEFAULT '0' COMMENT '个人有效帖子数',
  `ordinary_valid_info_users` int(11) DEFAULT '0' COMMENT '个人有效帖子人数',
  `ordinary_new_info_count` int(11) DEFAULT '0' COMMENT '个人新发帖子数',
  `ordinary_new_info_users` int(11) DEFAULT '0' COMMENT '个人新发帖子人数',
  `ordinary_modify_info_count` int(11) DEFAULT '0' COMMENT '个人修改帖子数',
  `ordinary_modify_info_users` int(11) DEFAULT '0' COMMENT '个人修改帖子人数',
  `ordinary_refresh_info_count` int(11) DEFAULT '0' COMMENT '个人刷新帖子数',
  `ordinary_refresh_info_users` int(11) DEFAULT '0' COMMENT '个人刷新帖子人数',
  `ordinary_new_valid_info_count` int(11) DEFAULT '0' COMMENT '个人新增有效帖子数',
  `ordinary_new_valid_info_users` int(11) DEFAULT '0' COMMENT '个人新增有效发帖人数',
  `vip_valid_info_count` int(11) DEFAULT '0' COMMENT 'VIP有效帖子数',
  `vip_valid_info_users` int(11) DEFAULT '0' COMMENT 'VIP有效帖子人数',
  `vip_new_info_count` int(11) DEFAULT '0' COMMENT 'VIP新发帖子数',
  `vip_new_info_users` int(11) DEFAULT '0' COMMENT 'VIP新发帖子人数',
  `vip_modify_info_count` int(11) DEFAULT '0' COMMENT 'VIP修改帖子数',
  `vip_modify_info_users` int(11) DEFAULT '0' COMMENT 'VIP修改帖子人数',
  `vip_refresh_info_count` int(11) DEFAULT '0' COMMENT 'VIP刷新帖子数',
  `vip_refresh_info_users` int(11) DEFAULT '0' COMMENT 'VIP刷新帖子人数',
  `vip_new_valid_info_count` int(11) DEFAULT '0' COMMENT 'VIP新增有效帖子数',
  `vip_new_valid_info_users` int(11) DEFAULT '0' COMMENT 'VIP新增有效发帖人数',
  PRIMARY KEY (`stat_date`,`local_id`,`cate_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
''' % (tablename)
    # mysqltool.droptable(dbh, tablename)
    query(dbh, q)

def transferFile(filename,channel_info,cate_infos,outfiles):
        f=open(filename,'r')
        for eachLine in f:
            fields = eachLine.strip().split(',')
#            if(len(fields)!=41):
#                with open('errorlog.txt','a') as errorfile:
#                    errorfile.write(filename+":"+eachLine)
            cateid=fields[0]
            if not cate_infos.get(cateid):
                with open('logs/errorData_%s.txt'%fields[1],'a') as errorfile:
                    errorfile.write(filename+":KeyError,"+','.join(fields[:])+'\n')
                outfiles['other'].write(','.join(fields[1:])+'\n')
                continue
            tablePrefix=channel_info[cate_infos[cateid]]
            row = ','.join(fields[1:]) + '\n'
            outfiles[tablePrefix].write(row)
        f.close()
def loadToMysql(outputPath,channel_info,day):
    conn = connect_to_base_dicts('dbwww58com_58_stats')
    for prefix in set(channel_info.values()):
        filename='%s/%s_post_detail.txt' % (outputPath,prefix)
        if os.path.getsize(filename):
            tablename = prefix+"_post_detail_%s" % day[:6]
            create_table(conn,tablename)
            loaddata = 'load data local infile "%s" into table %s CHARACTER SET utf8 fields terminated by "," LINES TERMINATED BY "\n";' % (filename, tablename)
            cmdtxt = 'echo \'%s\' | mysql --auto-rehash -h10.5.20.16 -P58885 -u"ec_stat" -p"L#c8UM5RGh5C" -D dbwww58com_58_stats' % (loaddata)
        #print cmdtxt
            os.system(cmdtxt)
        os.system('rm ' + filename)
    conn.close()
    
if __name__ == '__main__':
    if len(sys.argv) < 3:
        print 'usage: %s dataDirectory Date!' % (sys.argv[0])
        sys.exit(1)
    filepath = sys.argv[1]
    day=sys.argv[2]
    if os.path.exists(filepath) and os.path.isdir(filepath):
        channel_info = get_channel_info() 
        cate_infos = get_cateID_channelID()
        outfiles = {}
        outputPath=filepath+"/output"
        if  not os.path.exists(outputPath):
            os.makedirs(outputPath)
            print('make dirs %s succeed!' % outputPath)
        for prefix in set(channel_info.values()):
            filename = '%s/%s_post_detail.txt' % (outputPath,prefix)
            outfiles[prefix] = open(filename, 'w')
        files=os.listdir(filepath)
        for filename in files:
            if 'part-r-' in filename:
                print(filename)
                transferFile(filename, channel_info, cate_infos, outfiles)
                os.system('rm ' + filename)
        for filename in outfiles:
            outfiles[filename].flush()
            outfiles[filename].close()
        loadToMysql(outputPath,channel_info,day)
        loadSummaryToMysql.loadSummaryToMysql(day)
    sys.exit(0)
